package com.googlecode.cardamomo.dbreveng;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;

import com.googlecode.cardamomo.ermodel.Column;
import com.googlecode.cardamomo.ermodel.ForeignKey;
import com.googlecode.cardamomo.ermodel.Index;
import com.googlecode.cardamomo.ermodel.Key;
import com.googlecode.cardamomo.ermodel.Schema;
import com.googlecode.cardamomo.ermodel.Table;

/**
 * Class DBMetaDataLoader is a helper class for loading data model information
 * from a JDBC connection
 * 
 * @author jtorne
 *
 */
public class DBMetaDataLoader {

	private Connection con = null;
	private String catalogName = null;

	private String[] tableTypes = { "TABLE" };
	
	public DBMetaDataLoader(Connection connection) {
		setCon(connection);
	}

	/**
	 * This function returns an Schema object create with the information
	 * obtained from the JDBC connection for the named schema.
	 * 
	 * @param schemaName
	 * @param fullInfo
	 * @return Schema instance
	 * @throws SQLException
	 */
	public Schema getSchemaInfo(String schemaName, boolean fullInfo)
			throws SQLException {
		Schema sm = new Schema(schemaName);
		DatabaseMetaData dbmd = getCon().getMetaData();
		ResultSet schemars = dbmd.getSchemas();
		String schema = null;
		boolean found = false;
		while (schemars.next()) {
			ResultSetMetaData rsmd = schemars.getMetaData();
			schema = schemars.getString("TABLE_SCHEM");
			if (rsmd.getColumnCount() > 1)
				catalogName = schemars.getString("TABLE_CATALOG");
			else
				catalogName = "";
			if (schema != null && schema.equalsIgnoreCase(schemaName)) {
				found = true;
				break;
			}
		}
		schemars.close();
		if (!found)
			return sm;
		if (fullInfo) {
			loadTableInfo(sm, schemaName, null);
		}
		return sm;
	}

	public void loadTableInfo(Schema sm, String schemaPat, String tablePattern)
			throws SQLException {
		DatabaseMetaData dbmd = getCon().getMetaData();
		ResultSet tablers = dbmd.getTables(null, schemaPat, tablePattern, tableTypes);
		String table = null, tabletype = null, remarks = null;
		SQLException se = null;
		while (tablers.next()) {
			table = tablers.getString("TABLE_NAME");
			tabletype = tablers.getString("TABLE_TYPE");
			String schema = tablers.getString("TABLE_SCHEM");
			remarks = tablers.getString("REMARKS");
			if (schemaPat != null && !schemaPat.equalsIgnoreCase(schema)) {
				System.err.println("Wrong schema. Ignoring "+table);
				continue;
			}
			if (table != null && tabletype != null) {
				if (tabletype.equals("TABLE") /* || tabletype.equals("VIEW") */) {
					try {
						Table tm = loadTable(table, schema, tabletype, remarks, dbmd);
						if (sm != null) sm.addTable(tm);
					} catch (SQLException e) {
						se = e;
					}
				}
			}
			
		}
		tablers.close();
		if (se != null)
			throw se;
	}

	private Table loadTable(String table, String schema, 
			String tabletype, String remarks, DatabaseMetaData dbmd) 
			throws SQLException
	{
		if (table == null || table.length() == 0)
			return null;
		Table tm = new Table(table);
		String fullTableName = null;
		SQLException se = null;
		tm.setType(tabletype);
		tm.setComment(remarks);
		if (schema != null && schema.length() > 0)
			fullTableName = schema + "." + table;
		else
			fullTableName = table;
		se = null;
		try {
			Vector<Column> colinfo = new Vector<Column>();
			PreparedStatement ps = getCon()
					.prepareStatement("SELECT * FROM " + fullTableName
							+ " WHERE 1 <> 1");
			ResultSetMetaData rsmd = ps.getMetaData();
			for (int i = 0; i < rsmd.getColumnCount(); i++) {
				String colName = rsmd.getColumnName(i + 1);
				ResultSet rs = dbmd.getColumns(null, schema, table,	colName);
				Column cm = new Column(colName);
				if (rs.next()) {
					cm.setType(rs.getString("TYPE_NAME"));
					cm.setSize(rs.getInt("COLUMN_SIZE"));
					cm.setDecimals(rs.getInt("DECIMAL_DIGITS"));
					cm.setNullable((rs.getInt("NULLABLE") == ResultSetMetaData.columnNullable));
					if (cm.isNumeric()) cm.setSigned(true);
					cm.setComment(rs.getString("REMARKS"));
				} else
					System.err.println("Missing column info");
				colinfo.add(cm);
				rs.close();
			}

			tm.setColumns(colinfo);
			ps.close();
		} catch (SQLException e) {
			System.err.println("Exception processing columns: "+e.getLocalizedMessage());
			se = e;
		}

		loadPrimaryKeyInfo(dbmd, tm, schema);
		// get index info
		/*
		 * 

		 */
		
		loadIndexesInfo (dbmd, tm, schema);
		
		// get foreign keys info
		/*
		 * 

		 */
		loadForeignKeys (dbmd, tm, schema);
		if (se != null) {
			System.err.println("Exception processing table "+table+": "
					+se.getLocalizedMessage());
			throw se;
		}
		return tm;
	}

	private void loadPrimaryKeyInfo(DatabaseMetaData dbmd, Table tm, String schema)
		throws SQLException
	{
		// get primary key info
	
		SQLException se = null;
		
		try {
			ResultSet pkrs = dbmd.getPrimaryKeys(catalogName,
					schema, tm.getName());
			boolean morePK = pkrs.next();
			if (morePK) {
				Index pki = new Index();
				String pkname = null;
				while (morePK) {
					String cn = pkrs.getString("COLUMN_NAME");
					short ks = pkrs.getShort("KEY_SEQ");
					String pkn = pkrs.getString("PK_NAME");
					if (pkn == null || pkn.equalsIgnoreCase(tm.getName()))
						pkn = "PK_"+tm.getName();
					if (pkname == null) pkname = pkn;
					pki.addKey(ks, cn, false);
					morePK = pkrs.next();
				}
				pki.setName(pkname);
				tm.setPrimaryKeyIndex(pki);
			}
			pkrs.close();
		} catch (SQLException e) {
			System.err.println("Exception processing primary key of "
							+ tm.getName()	+ ": " + e.getLocalizedMessage());
			se = e;
		}
	
		if (se != null)
			throw se;
	}

	/**
	 * Load from database information about indexes of a given table
	 * 
	 * @param dbmd
	 * @param tm
	 * @param schema
	 * @throws SQLException
	 */
	private void loadIndexesInfo(DatabaseMetaData dbmd, Table tm, String schema)
		throws SQLException
	{
		SQLException se = null;
		ResultSet iirs = null;
		try {
			iirs = dbmd.getIndexInfo(catalogName, schema,
					tm.getName(), false, true);
			while (iirs.next()) {
				short type = iirs.getShort("TYPE");
				if (type == DatabaseMetaData.tableIndexStatistic)
					continue;
				String iname = iirs.getString("INDEX_NAME");
				short ordinal = iirs.getShort("ORDINAL_POSITION");
				String cname = iirs.getString("COLUMN_NAME");
				boolean notUnique = iirs.getBoolean("NON_UNIQUE");
				String ascDesc = iirs.getString("ASC_OR_DESC");
				boolean asc = true;
				// validate the index info
				if (ascDesc != null
						&& ascDesc.startsWith("D"))
					asc = false;
				String filter = iirs.getString("FILTER_CONDITION");
				if (iname != null) {
					Index im = tm.getIndex(iname);
					im.setUnique(!notUnique);
					im.addKey(ordinal, cname, asc);
					im.setFilter(filter);
					tm.addIndex(im);
				} else {
					System.err.println("Unnamed index contains column "
									+ cname + " at " + ordinal);
				}
				
			}

		} catch (SQLException e) {
			System.err.println("Exception processing indexes of "
					+ tm.getName() + ": " + e.getLocalizedMessage());
			se = e;
		} finally {
			if (iirs != null)
				iirs.close();
		}
		if (se != null)
			throw se;
	}

	/**
	 * Load from Database information about foreign keys of a given table
	 * 
	 * @param dbmd	Database MetaData object
	 * @param tm	Table Model object (information loaded into it)
	 * @param schema
	 * @throws SQLException
	 */
	private void loadForeignKeys(DatabaseMetaData dbmd, Table tm, String schema)
		throws SQLException
	{
		ResultSet iirs = null;
		SQLException se = null;
		try {
			iirs = dbmd.getImportedKeys(catalogName, schema,
					tm.getName());
			while (iirs.next()) {
				String parentTable = iirs.getString("PKTABLE_NAME");
				String parentkname = iirs.getString("PKCOLUMN_NAME");
				String foreignColumn = iirs.getString("FKCOLUMN_NAME");
				String fkname = iirs.getString("FK_NAME");
				int keySeq = iirs.getShort("KEY_SEQ");
				ForeignKey fk = tm.getForeignKey(fkname);
				fk.setParentTable(parentTable);
				Key k = new Key(keySeq, foreignColumn, true);
				fk.getChildKeys().add(k);
				Key pk = new Key(keySeq, parentkname, true);
				fk.getParentKeys().add(pk);
			}
			iirs.close();
		} catch (SQLException e) {
			System.err.println("Exception processing foreign keys of "
					+ tm.getName()	+ ": " + e.getLocalizedMessage());
			se = e;
		} finally {
			if (iirs != null) iirs.close();
		}
		if (se != null)
			throw se;
	}

	public String getCatalogName() {
		return catalogName;
	}

	public Connection getCon() {
		return con;
	}

	public void setCon(Connection con) {
		this.con = con;
	}

}
